package com.zhi.dao;

import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import com.zhi.entity.User;
import org.springframework.transaction.annotation.Transactional;

public interface UserRepository extends JpaRepository<User, Integer>{
	
	@Query("SELECT a.id,a.username,a.password,"
			+ "(SELECT b.codeName from Dictionary b WHERE a.userType=b.code and b.codeType='userType')"
			+ " FROM User a")
	public List<Object[]> findAllWithTypeName();
	
	@Query(value = "SELECT a.id,a.username,a.password,"
			+ "(SELECT b.code_Name from Dictionary b WHERE a.user_type=b.code and b.code_Type='userType') user_Type,"
			+ "(SELECT b.code_Name from Dictionary b WHERE a.user_State=b.code and b.code_Type='userState') user_State,"
			+ "a.user_sex,"
			+ "a.user_age"
			+ " FROM User a",
			countQuery ="select count(*) from User",
			nativeQuery = true)
	public Page<User> findAllWithTypeName(Pageable pageable);

	/**
	 * 根据用户名和用户类型删除登录用户信息，使用Query注解删除修改时，需添加Modifying、Transactional
	 * @param username
	 * @param userType
	 */
	@Modifying
	@Transactional
	@Query(value = "delete from user where username = ?1 and user_type = ?2 ",nativeQuery = true)
	public void deleteByUsernameAndUserType(String username,String userType);

	/**
	 * 根据用户名查找用户
	 * @param username1
	 * @return
	 */
	@Query(value = "select * from user where username = ?1",
	nativeQuery = true)
	public User findByName(String username1);
}
